Skip to main content

Calculated Fields

Calculated fields are field types that don't require any user entry and are populated automatically. They may be used to calculate totals, automatically populate text fields based on logical formulas or lookups, or generate automatic data like reference numbers.

Some general rules about calculated fields are:

  • You can refer to other fields by their ID, which by default is the field label, but in lower case and with spaces replaced by underscores. So a field labelled 'First Name' would by default have an ID of 'first_name'. However, this is not always the case (e.g. if a field label is changed after it is created), so you can always check the correct ID simply by clicking on the field you are wanting to reference while in the form editor.
  • When incorporating fixed text into a formula, you should enclose it within single or double-quotes. So referring to first_name in a formula would contain the contents of the first name field, whereas referring to 'first_name' would be taken to mean first_name as a fixed piece of text.
  • Formula keywords are not case-sensitive, so 'sum' 'Sum' or 'SUM' would all be fine for a sum formula.
  • Values are updated dynamically unless otherwise stated, so users can see the results as they are entering values to the input fields.
  • Formulas can be 'nested' within brackets, there are examples of this below.

The different calculated field types available are:

Auto Number​

This generates a sequential number which will be a unique reference for each transaction within this app. Use the fixed length setting to pad the number with leading zero's if required, the option to prefix with a fixed reference is also available. In the example below, these settings:

alt image

Will produce results of FS-00001, FS-00002 etc. If you need a variable prefix you can link it to a field ID instead by entering the ID here without quotes around it. The actual sequence number will only be generated when the first step of the process has been submitted - until this point the transaction has 'draft' status and can be abandoned by the user if they don't wish to continue it for any reason.

Text​

A calculated text field will allow you to take text from another field or a fixed value contained in double or single quotes and use various formulas on it. Any formula listed with an output type of 'text' can be used within a text field. In the examples listed, you can replace any of the fixed text with a form field ID to make the output dependent on the values entered or selected in a particular field.

Number​

Number field formulas contain numeric output, and can contain any formula listed that has an output of numeric, as well as basic mathematical operations such as + - * / (plus, minus, multiply, divide). So if you have a field IDs net_amount, tax_amount and gross_amount you can use the following formula in the gross_amount field to calculate the value:

net_amount + tax_amount

Date / Time / Date & Time​

These field types store dates, times or dates and times respectively. These can be used in conjunction with the formula that have the date or date/time output.

Email​

This is similar to a text field, but the value generated by the formula should be in a valid email format.

Barcode​

This will generate a barcode based on the output of a text formula. The code generated is in the commonly used 'Code 128' format which allows for alpha-numeric characters plus some 'special' characters such as dashes, hash signs etc. To generate a barcode based on an existing text field, simply enter that field ID in the barcode formula.

User​

A user field allows for a FinanSys Apps user to be populated automatically. Use the 'Filters' in the advanced properties tab to filter by either name or email address. The value calculated can then be used in a step of the process, for example to decide who should do the next approval step.

tip

You can use this type of step in conjunction with reference data. For example, a user may select a department from a list of reference data which also includes a field for 'department manager'. A calculated user field can then retrieve the department manager based on the department selection and use this in the following approval step.

External​

A calculated external field brings back data extracted from an external lookup. This is linked to a data connector in the same way as an external dropdown field, which must already be configured. If more than one value is returned, the first value is automatically selected but others are available for selection in the same way as a regular external dropdown, so this type of field can be used either where only one value is expected or you want the first value to be selected by default.

Reference​

A calculated reference field brings back data from a set of reference data or from transactional data held in another app. As with the external calculated field, if more than one value is returned it will default to the first value but others will be available for selection. You can use the filters on the advanced properties tab to narrow down what items are selected, so you can use this type of field where you want to look up reference data based on the value of another field. For example, if a user enters a currency code you could look up the currency name and exchange rate from a set of reference data containing these fields.